1 Imports System.Data.SqlClient
2 Public Class frmSubCategory
3 Sub fillCombo()
4 Try
5 con = New SqlConnection(cs)
6 con.Open()
7 adp = New SqlDataAdapter()
8 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(CategoryName) FROM Category", con)
9 ds = New DataSet("ds")
10 adp.Fill(ds)
11 dtable = ds.Tables(0)
12 cmbCategory.Items.Clear()
13 For Each drow As DataRow In dtable.Rows
14 cmbCategory.Items.Add(drow(0).ToString())
15 Next
16 Catch ex As Exception
17 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
18 End Try
19 End Sub
20 Sub Reset()
21 cmbCategory.SelectedIndex = -1
22 txtSearchByCategory.Text = ""
23 txtSearchBySubCategory.Text = ""
24 txtSubCategory.Text = ""
25 txtSubCategory.Focus()
26 btnSave.Enabled = True
27 btnUpdate.Enabled = False
28 btnDelete.Enabled = False
29 Getdata()
30 auto()
31 End Sub
32 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
33 Me.Close()
34 End Sub
35
36 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
37 If Len(Trim(txtSubCategory.Text)) = 0 Then
38 MessageBox.Show("Please enter sub category", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
39 txtSubCategory.Focus()
40 Exit Sub
41 End If
42 If Len(Trim(cmbCategory.Text)) = 0 Then
43 MessageBox.Show("Please select category", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
44 cmbCategory.Focus()
45 Exit Sub
46 End If
47 Try
48 con = New SqlConnection(cs)
49 con.Open()
50 Dim ct As String = "select SubCategoryName,Category from SubCategory where SubCategoryName=@d1 and Category=@d2"
51 cmd = New SqlCommand(ct)
52 cmd.Connection = con
53 cmd.Parameters.AddWithValue("@d1", txtSubCategory.Text)
54 cmd.Parameters.AddWithValue("@d2", cmbCategory.Text)
55 rdr = cmd.ExecuteReader()
56
57 If rdr.Read() Then
58 MessageBox.Show("Record Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
59 txtSubCategory.Text = ""
60 txtSubCategory.Focus()
61 If (rdr IsNot Nothing) Then
62 rdr.Close()
63 End If
64 Return
65 End If
66
67 con = New SqlConnection(cs)
68 con.Open()
69
70 Dim cb As String = "insert into SubCategory(SubCategoryName,Category,ID) VALUES (@d1,@d2," & txtID.Text & ")"
71 cmd = New SqlCommand(cb)
72 cmd.Connection = con
73 cmd.Parameters.AddWithValue("@d1", txtSubCategory.Text)
74 cmd.Parameters.AddWithValue("@d2", cmbCategory.Text)
75 cmd.ExecuteReader()
76 con.Close()
77 LogFunc(lblUser.Text, "added the new subcategory '" & txtSubCategory.Text & "' having Category '" & cmbCategory.Text & "'")
78 MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
79 btnSave.Enabled = False
80 Getdata()
81 Catch ex As Exception
82 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
83 End Try
84 End Sub
85
86 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
87 Try
88 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
89 DeleteRecord()
90 End If
91 Catch ex As Exception
92 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
93 End Try
94 End Sub
95 Private Sub DeleteRecord()
96
97 Try
98
99 Dim RowsAffected As Integer = 0
100 con = New SqlConnection(cs)
101 con.Open()
102 Dim cl As String = "select SubCategoryID from Product,SubCategory where Product.SubCategoryID=SubCategory.ID and SubCategoryID=@d1"
103 cmd = New SqlCommand(cl)
104 cmd.Connection = con
105 cmd.Parameters.AddWithValue("@d1", txtID.Text)
106 rdr = cmd.ExecuteReader()
107 If rdr.Read Then
108 MessageBox.Show("Unable to delete..Already in use in Product Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
109 If Not rdr Is Nothing Then
110 rdr.Close()
111 End If
112 Exit Sub
113 End If
114 con = New SqlConnection(cs)
115 con.Open()
116 Dim cq As String = "delete from SubCategory where ID=@d1"
117 cmd = New SqlCommand(cq)
118 cmd.Connection = con
119 cmd.Parameters.AddWithValue("@d1", txtID.Text)
120 RowsAffected = cmd.ExecuteNonQuery()
121 If RowsAffected > 0 Then
122 LogFunc(lblUser.Text, "deleted the subcategory '" & txtSubCategory.Text & "' having Category '" & cmbCategory.Text & "'")
123 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
124 Getdata()
125 Reset()
126 Else
127 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
128 Reset()
129 End If
130 If con.State = ConnectionState.Open Then
131 con.Close()
132
133 End If
134 Catch ex As Exception
135 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
136 End Try
137 End Sub
138
139 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
140 Try
141 If Len(Trim(txtSubCategory.Text)) = 0 Then
142 MessageBox.Show("Please enter Sub Category name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
143 txtSubCategory.Focus()
144 Exit Sub
145 End If
146 If Len(Trim(cmbCategory.Text)) = 0 Then
147 MessageBox.Show("Please select Category", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
148 cmbCategory.Focus()
149 Exit Sub
150 End If
151 con = New SqlConnection(cs)
152 con.Open()
153 Dim cb As String = "update SubCategory set SubCategoryName=@d1,Category=@d2 where ID=" & txtID.Text & ""
154 cmd = New SqlCommand(cb)
155 cmd.Connection = con
156 cmd.Parameters.AddWithValue("@d1", txtSubCategory.Text)
157 cmd.Parameters.AddWithValue("@d2", cmbCategory.Text)
158 cmd.ExecuteReader()
159 con.Close()
160 LogFunc(lblUser.Text, "updated the sub category '" & txtSubCategory.Text & "' having Category '" & cmbCategory.Text & "'")
161 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
162 btnUpdate.Enabled = False
163 Getdata()
164 Catch ex As Exception
165 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
166 End Try
167 End Sub
168 Public Sub Getdata()
169 Try
170 con = New SqlConnection(cs)
171 con.Open()
172 cmd = New SqlCommand("SELECT RTRIM(ID),RTRIM(SubCategoryName), RTRIM(Category) from SubCategory order by SubCategoryName", con)
173 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
174 dgw.Rows.Clear()
175 While (rdr.Read() = True)
176 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
177 End While
178 con.Close()
179 Catch ex As Exception
180 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
181 End Try
182 End Sub
183 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
184 Reset()
185 End Sub
186
187
188 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
189 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
190 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
191 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
192 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
193 End If
194 Dim b As Brush = SystemBrushes.ControlText
195 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
196
197 End Sub
198 Private Sub auto()
199 Try
200 Dim Num As Integer = 0
201 con = New SqlConnection(cs)
202 con.Open()
203 Dim sql As String = ("SELECT MAX(ID) FROM SubCategory")
204 cmd = New SqlCommand(sql)
205 cmd.Connection = con
206 If (IsDBNull(cmd.ExecuteScalar)) Then
207 Num = 1
208 txtID.Text = Num.ToString
209 Else
210 Num = cmd.ExecuteScalar + 1
211 txtID.Text = Num.ToString
212 End If
213 cmd.Dispose()
214 con.Close()
215 con.Dispose()
216 Catch ex As Exception
217 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
218 End Try
219 End Sub
220 Private Sub frmCategory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
221 Getdata()
222 fillCombo()
223 End Sub
224
225 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
226 Try
227 If dgw.Rows.Count > 0 Then
228 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
229 txtSubCategory.Text = dr.Cells(1).Value.ToString()
230 txtID.Text = dr.Cells(0).Value.ToString()
231 cmbCategory.Text = dr.Cells(2).Value.ToString()
232 btnUpdate.Enabled = True
233 btnDelete.Enabled = True
234 btnSave.Enabled = False
235 End If
236 Catch ex As Exception
237 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
238 End Try
239 End Sub
240
241 Private Sub txtSearchByCategory_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchByCategory.TextChanged
242 Try
243 con = New SqlConnection(cs)
244 con.Open()
245 cmd = New SqlCommand("SELECT RTRIM(ID), RTRIM(SubCategoryName), RTRIM(Category) from SubCategory where Category like '%" & txtSearchByCategory.Text & "%' order by Category", con)
246 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
247 dgw.Rows.Clear()
248 While (rdr.Read() = True)
249 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
250 End While
251 con.Close()
252 Catch ex As Exception
253 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
254 End Try
255 End Sub
256
257 Private Sub txtSearchBySubCategory_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchBySubCategory.TextChanged
258 Try
259 con = New SqlConnection(cs)
260 con.Open()
261 cmd = New SqlCommand("SELECT RTRIM(ID), RTRIM(SubCategoryName), RTRIM(Category) from SubCategory where SubCategoryName like '%" & txtSearchBySubCategory.Text & "%' order by SubCategoryName", con)
262 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
263 dgw.Rows.Clear()
264 While (rdr.Read() = True)
265 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
266 End While
267 con.Close()
268 Catch ex As Exception
269 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
270 End Try
271 End Sub
272
273 Private Sub cmbCategory_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbCategory.Format
274 If (e.DesiredType Is GetType(String)) Then
275 e.Value = e.Value.ToString.Trim
276 End If
277 End Sub
278 End Class
